package com.upeoe.shiro.chapter5.dao.impl;

import com.upeoe.shiro.chapter5.dao.UserDao;
import com.upeoe.shiro.chapter5.entity.User;
import com.upeoe.shiro.chapter5.utils.JdbcTemplateUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * @author ven_hxz
 * @create 2016-07-05 上午11:33
 */
public class UserDaoImpl implements UserDao {

    private JdbcTemplate jdbcTemplate = JdbcTemplateUtils.jdbcTemplate();

    public User createUser(final User user) {
        final String sql = "insert into sys_users(username, password, salt, locked) values(?,?,?,?)";
        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql, new String[]{"id"});
                ps.setString(1, user.getUsername());
                ps.setString(2, user.getPassword());
                ps.setString(3, user.getSalt());
                ps.setBoolean(4, user.getLocked());
                return ps;
            }
        }, keyHolder);
        user.setId(keyHolder.getKey().longValue());
        return user;
    }

    public void updateUser(User user) {
        String sql = "update sys_users set username = ?, password = ?, salt = ?, locked = ? where id = ?";
        jdbcTemplate.update(sql, user.getUsername(), user.getPassword(),
                user.getSalt(), user.getLocked(), user.getId());
    }

    public void deleteUser(Long userId) {
        String sql = "delete from sys_users where id = ?";
        jdbcTemplate.update(sql, userId);
    }

    public void addRelationRoles(Long userId, Long... roleIds) {
        if (roleIds == null || roleIds.length == 0) return;

        String sql = "insert into sys_users_roles(user_id, role_id) values(?, ?)";

        for (Long roleId : roleIds) {
            if (!isExists(userId, roleId)) {
                jdbcTemplate.update(sql, userId, roleId);
            }
        }
    }

    public void removeRelationRoles(Long userId, Long... roleIds) {
        if (roleIds == null || roleIds.length == 0) return;

        String sql = "delete from sys_users_roles where user_id = ? and role_id = ?";

        for (Long roleId : roleIds) {
            if (isExists(userId, roleId)) {
                jdbcTemplate.update(sql, userId, roleId);
            }
        }
    }

    public User findUserById(Long userId) {
        String sql = "select id, username, password, salt, locked from sys_users where id = ?";
        List<User> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper(User.class), userId);

        return users.size() != 0 ? users.get(0) : null;
    }

    public User findByUsername(String username) {
        String sql = "select id, username, password, salt, locked from sys_users where username = ?";
        List<User> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class), username);

        return users.size() != 0 ? users.get(0) : null;
    }

    public Set<String> findRoles(String username) {
        String sql = "select role from sys_users u, sys_roles r, sys_users_roles ur where " +
                "u.username = ? and u.id = ur.user_id and r.id = ur.role_id";
        return new HashSet<String>(jdbcTemplate.queryForList(sql, String.class, username));
    }

    public Set<String> findPermissions(String username) {
        String sql = "select permission from " +
                "sys_users u, sys_roles r, " +
                "sys_permissions p, sys_users_roles ur, " +
                "sys_roles_permissions rp where " +
                "u.username = ? and u.id = ur.user_id and " +
                "r.id = ur.role_id and r.id = rp.role_id and " +
                "p.id = rp.permission_id";
        return new HashSet<String>(jdbcTemplate.queryForList(sql, String.class, username));
    }

    private boolean isExists(Long userId, Long roleId) {
        String sql = "select count(1) from sys_users_roles where user_id = ? and role_id = ?";
        return jdbcTemplate.queryForObject(sql, Integer.class, userId, roleId) != 0;
    }
}
